package com.acwebsitedesign.uRateDrugs.domain;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

import com.acwebsitedesign.uRateDrugs.util.DbUtils;

@Component("ReviewRepository")
public class ReviewRepositoryImpl implements ReviewRepository{
	
	private SimpleJdbcTemplate simpleJdbcTemplate = DbUtils.getSimpleJdbcTemplate();

	@SuppressWarnings("unchecked")
	public List<Review> getReviewsForADrug(int drugId) {
		String sql = "select r.id, r.rating, r.reasonfortaking, r.reviewtext, r.datecreated, r.positivereview, rd.user_id, rd.drug_id from review r, reviewdetail rd where drug_id=? AND rd.review_id=r.id ORDER BY r.id DESC";
				return simpleJdbcTemplate.query(sql, new ReviewMapper(), drugId);
	}
	
	@SuppressWarnings("unchecked")
	public List<Review> getReviewWithId (Review review) {
		String sql = "select r.id, r.rating, r.reasonfortaking, r.reviewtext, r.datecreated, r.positivereview from review r where r.rating=? and r.reasonfortaking=? and r.reviewtext=?"; 	
		return simpleJdbcTemplate.query(sql, new PartialReviewMapper(), review.getRating(), review.getReasonForTaking(), review.getReviewText());
	}


	public void saveReview(Review review) {
		String sql1 = "insert into review (rating, reasonfortaking, reviewtext, positivereview, datecreated) values (?,?,?,?,?)";
		String sql2 = "insert into reviewdetail (drug_id, user_id, review_id) values (?,?,?)";
		Date date = new java.util.Date();
		simpleJdbcTemplate.update(sql1, review.getRating(), review.getReasonForTaking(), review.getReviewText(), review.getPositiveReview(), date);
		
		List <Review> reviewList = getReviewWithId(review);
		Review review2 = reviewList.get(0);
		simpleJdbcTemplate.update(sql2, review.getDrug_id(), review.getUser_id(), review2.getId());
	}
	        
	public void removeReview(int id) {
			String sql1 = "DELETE FROM review WHERE id=?";
			String sql2 = "delete from reviewdetail where review_id=?";
			simpleJdbcTemplate.update(sql1, id);
			simpleJdbcTemplate.update(sql2, id);
		}
	
	public void removeAllReviewsForAUser(int user_id) {
		List <Review> reviewList = getAllReviewsFromAUser(user_id);
		for (Review review : reviewList) {
			int reviewId = review.getId();
			removeReview(reviewId);
		}
	}

	@SuppressWarnings("unchecked")
	public List<Review> getAllReviewsFromAUser(int id) {	
		String sql = "SELECT r.id, r.rating, r.reasonForTaking, r.reviewText, r.datecreated, r.positiveReview, user_id, drug_id FROM review r, reviewdetail rd WHERE rd.review_id=r.id and rd.user_id=?";
				return simpleJdbcTemplate.query(sql, new ReviewMapper(), id);
	}
	
	
	@SuppressWarnings("unchecked")
	private static final class ReviewMapper implements RowMapper {
	        public Review mapRow(ResultSet rs, int rowNum) throws SQLException {
	        	int id = rs.getInt("r.id");
	        	int rating = rs.getInt("r.rating");
	        	String reasonForTaking = rs.getString("r.reasonForTaking");
	        	String reviewText = rs.getString("r.reviewText");
	        	Date date = rs.getDate("r.datecreated");
	        	Boolean positiveReview = rs.getBoolean("r.positiveReview");
	        	int user_id = rs.getInt("rd.user_id");
	        	int drug_id = rs.getInt("rd.drug_id");
	        	
	        	Review review = new Review(id, rating, reasonForTaking, reviewText, date, positiveReview, user_id, drug_id);
	        	return review;
	        }
	}
	
	public int getRating(int drugId) {
		int total = 0;
		int averageRating = 0;
		int size = 0;
		List<Review> reviewList = getReviewsForADrug(drugId);
		if (!(reviewList.isEmpty())){
			size = reviewList.size();
			for (Review review : reviewList){
				int rating = review.getRating();
				total = total + rating;
			}	
			averageRating = total/size;
		}
		return averageRating;
	}
	
	@SuppressWarnings("unchecked")
	private static final class PartialReviewMapper implements RowMapper {
        public Review mapRow(ResultSet rs, int rowNum) throws SQLException {
        	int id = rs.getInt("r.id");
        	int rating = rs.getInt("r.rating");
        	String reasonForTaking = rs.getString("r.reasonForTaking");
        	String reviewText = rs.getString("r.reviewText");
        	Date date = rs.getDate("r.datecreated");
        	Boolean positiveReview = rs.getBoolean("r.positiveReview");
        	
        	Review review = new Review(id, rating, reasonForTaking, reviewText, date, positiveReview);
        	return review;
        }
	}
}
